/***********************************************************************************************************************************
 * Tables_Allow-Page-Locks-ON.sql
 * Frank Figearo
 * Generate T-SQL to set ALLOW_PAGE_LOCKS=ON for all indexes on all tables in the current database.
 */
SET IMPLICIT_TRANSACTIONS OFF
--USE master
GO
--CREATE PROCEDURE ##PageLocksON AS
SELECT tsql_command= 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(t.name) + ' SET (ALLOW_PAGE_LOCKS=ON)'
  FROM sys.indexes i
	INNER JOIN sys.tables t ON (i.object_id = t.object_id)
  WHERE i.allow_page_locks = 0 AND i.name NOT LIKE 'queue_%_index'
GO
--EXECUTE sys.sp_MSforeachdb 'USE [?]; EXEC ##PageLocksON'
--DROP PROCEDURE ##PageLocksON
GO